﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NewBee.Core.Data;
using NewBee.Core.Data.Model;
using System.Collections;

namespace NewBee.Core.DataService
{
    public class BaseDataService
    {
        /// <summary>
        /// 
        /// </summary>
        public virtual DbHelper _DbHelper
        {
            get
            {
                return new DbHelper(DataConnection.ConnectionString, DataConnection.QueryConnectionString,
                    DataConnection.DriverType);
            }
        }

        public virtual string TableName { set; get; }

        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public bool ColumnExists(string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + TableName + "') and [name]='" + columnName + "'";
            object res = _DbHelper.Execute(sql, null);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }

        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public bool TabExists()
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            object obj = _DbHelper.Execute(strsql, null);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary> 
        /// 分页查询 
        /// </summary> 
        /// <param name="_PageParam"></param> 
        /// <returns></returns> 
        public PageResult GetBasePageList(PageParam _PageParam)
        {
            PageResult _PageResult = new PageResult();
            _PageResult.total = Count(_PageParam.where, _PageParam.param);
            _PageResult.rows = this._DbHelper
            .Select<dynamic>(TableName, _PageParam.select)
            .From(TableName)
            .Where(_PageParam.where)
            .Page(_PageParam.page, _PageParam.rows)
            .OrderBy(_PageParam.sort + " " + _PageParam.order)
            .Parameters(_PageParam.param)
            .Query();
            return _PageResult;
        }

        /// <summary>
        /// 返回查询条数
        /// </summary>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Count(string where, object param)
        {
            string sql = "Select count(*) From " + TableName;
            if (!string.IsNullOrEmpty(where))
                sql += " Where " + where;
            return this._DbHelper.Sql(sql).Parameter(param).Count();
        }

        /// <summary>
        /// 返回查询列表
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public IEnumerable<T> GetBaseList<T>(QueryParam queryParam)
        {
            if (string.IsNullOrEmpty(queryParam.select))
                queryParam.select = " * ";
            string _where = string.Empty;
            if (!string.IsNullOrEmpty(queryParam.where))
                _where += " Where " + queryParam.where;

            string _orderBy = string.Empty;
            if (!string.IsNullOrEmpty(queryParam.sort))
                _orderBy += " Order By " + queryParam.sort + " " + queryParam.order;

            string _top = string.Empty;
            if (queryParam.top >= 0)
                _top = "Top " + queryParam.top + " ";

            return this._DbHelper.Sql(@"Select " + _top + " " + queryParam.select + " From " + TableName + " " + _where + " " + _orderBy)
                .Parameters(queryParam.param)
                .Query<T>();
        }

        /// <summary>
        ///  执行增删改sql语句
        /// </summary>
        /// <param name="sqlHelper">SQL帮助类</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int BaseExecuteBySql(string sql, object param)
        {
            return _DbHelper.Execute(sql, param);
        }


        /// <summary>
        /// 执行SQL，返回实体数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public T GetBaseBySql<T>(string sql, object param)
        {
            return _DbHelper.QueryFirst<T>(sql, param);
        }

        /// <summary>
        /// 执行SQL，返回数据实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> GetBaseListBySql<T>(string sql, object param)
        {
            return _DbHelper.Query<T>(sql, param);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="where">条件</param>
        /// <param name="parameter">条件参数</param>
        /// <returns></returns>
        public int Delete(string where, params object[] parameter)
        {
            return _DbHelper.Delete(TableName).Where(where).Parameters(parameter).Execute();
        }


        /// <summary>
        /// 使用实体新增数据(主键自增)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="primaryKey"></param>
        /// <returns></returns>
        public int Insert<T>(T model, string primaryKey)
        {
            return _DbHelper.Insert<T>(model, primaryKey).Execute();
        }

        /// <summary>
        /// 使用实体新增数据(主键Guid)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="primaryKey"></param>
        /// <returns></returns>
        public int Insert<T>(T model)
        {
            return _DbHelper.Insert<T>(model).Execute();
        }

        /// <summary>
        /// 使用实体更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="where"></param>
        /// <param name="primaryKey"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int Update<T>(T model, string where, object[] primaryKey, params object[] parameters)
        {
            return _DbHelper.Update<T>(model).PrimaryKeys(primaryKey).Where(where).Parameters(parameters).Execute();
        }
    }
}
